package com.entertainment.ticketing.repository;

import com.entertainment.ticketing.entity.EventInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface EventInfoRepository extends JpaRepository<EventInfo, Long>, JpaSpecificationExecutor<EventInfo> {

    @Query(value = """
            SELECT e.* FROM event_info e
            LEFT JOIN ticket_info t ON t.event_id = e.id AND t.status = 1
            WHERE (:keyword IS NULL OR LOWER(e.title) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(CAST(e.description AS CHAR)) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(e.venue) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(e.address) LIKE LOWER(CONCAT('%', :keyword, '%')))
              AND (:category IS NULL OR LOWER(e.category) = LOWER(:category))
              AND (:status IS NULL OR e.status = :status)
              AND (:startFrom IS NULL OR e.start_time >= :startFrom)
              AND (:startTo IS NULL OR e.start_time <= :startTo)
            GROUP BY e.id
            ORDER BY CASE WHEN MIN(t.price) IS NULL THEN 999999999 ELSE MIN(t.price) END ASC
            """,
            countQuery = """
            SELECT COUNT(*) FROM (
                SELECT e.id FROM event_info e
                LEFT JOIN ticket_info t ON t.event_id = e.id AND t.status = 1
                WHERE (:keyword IS NULL OR LOWER(e.title) LIKE LOWER(CONCAT('%', :keyword, '%'))
                       OR LOWER(CAST(e.description AS CHAR)) LIKE LOWER(CONCAT('%', :keyword, '%'))
                       OR LOWER(e.venue) LIKE LOWER(CONCAT('%', :keyword, '%'))
                       OR LOWER(e.address) LIKE LOWER(CONCAT('%', :keyword, '%')))
                  AND (:category IS NULL OR LOWER(e.category) = LOWER(:category))
                  AND (:status IS NULL OR e.status = :status)
                  AND (:startFrom IS NULL OR e.start_time >= :startFrom)
                  AND (:startTo IS NULL OR e.start_time <= :startTo)
                GROUP BY e.id
            ) x
            """,
            nativeQuery = true)
    Page<EventInfo> searchOrderByLowestPriceAsc(
            @Param("keyword") String keyword,
            @Param("category") String category,
            @Param("status") Byte status,
            @Param("startFrom") java.time.LocalDateTime startFrom,
            @Param("startTo") java.time.LocalDateTime startTo,
            Pageable pageable
    );

    @Query(value = """
            SELECT e.* FROM event_info e
            LEFT JOIN ticket_info t ON t.event_id = e.id AND t.status = 1
            WHERE (:keyword IS NULL OR LOWER(e.title) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(CAST(e.description AS CHAR)) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(e.venue) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(e.address) LIKE LOWER(CONCAT('%', :keyword, '%')))
              AND (:category IS NULL OR LOWER(e.category) = LOWER(:category))
              AND (:status IS NULL OR e.status = :status)
              AND (:startFrom IS NULL OR e.start_time >= :startFrom)
              AND (:startTo IS NULL OR e.start_time <= :startTo)
            GROUP BY e.id
            ORDER BY CASE WHEN MIN(t.price) IS NULL THEN -1 ELSE MIN(t.price) END DESC
            """,
            countQuery = """
            SELECT COUNT(*) FROM (
                SELECT e.id FROM event_info e
                LEFT JOIN ticket_info t ON t.event_id = e.id AND t.status = 1
                WHERE (:keyword IS NULL OR LOWER(e.title) LIKE LOWER(CONCAT('%', :keyword, '%'))
                       OR LOWER(CAST(e.description AS CHAR)) LIKE LOWER(CONCAT('%', :keyword, '%'))
                       OR LOWER(e.venue) LIKE LOWER(CONCAT('%', :keyword, '%'))
                       OR LOWER(e.address) LIKE LOWER(CONCAT('%', :keyword, '%')))
                  AND (:category IS NULL OR LOWER(e.category) = LOWER(:category))
                  AND (:status IS NULL OR e.status = :status)
                  AND (:startFrom IS NULL OR e.start_time >= :startFrom)
                  AND (:startTo IS NULL OR e.start_time <= :startTo)
                GROUP BY e.id
            ) x
            """,
            nativeQuery = true)
    Page<EventInfo> searchOrderByLowestPriceDesc(
            @Param("keyword") String keyword,
            @Param("category") String category,
            @Param("status") Byte status,
            @Param("startFrom") java.time.LocalDateTime startFrom,
            @Param("startTo") java.time.LocalDateTime startTo,
            Pageable pageable
    );

    @Query(value = """
            SELECT e.* FROM event_info e
            LEFT JOIN order_info o ON o.event_id = e.id AND o.status = 2
            WHERE (:keyword IS NULL OR LOWER(e.title) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(CAST(e.description AS CHAR)) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(e.venue) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(e.address) LIKE LOWER(CONCAT('%', :keyword, '%')))
              AND (:category IS NULL OR LOWER(e.category) = LOWER(:category))
              AND (:status IS NULL OR e.status = :status)
              AND (:startFrom IS NULL OR e.start_time >= :startFrom)
              AND (:startTo IS NULL OR e.start_time <= :startTo)
            GROUP BY e.id
            ORDER BY COALESCE(SUM(o.ticket_count), 0) ASC
            """,
            countQuery = """
            SELECT COUNT(*) FROM (
                SELECT e.id FROM event_info e
                LEFT JOIN order_info o ON o.event_id = e.id AND o.status = 2
                WHERE (:keyword IS NULL OR LOWER(e.title) LIKE LOWER(CONCAT('%', :keyword, '%'))
                       OR LOWER(CAST(e.description AS CHAR)) LIKE LOWER(CONCAT('%', :keyword, '%'))
                       OR LOWER(e.venue) LIKE LOWER(CONCAT('%', :keyword, '%'))
                       OR LOWER(e.address) LIKE LOWER(CONCAT('%', :keyword, '%')))
                  AND (:category IS NULL OR LOWER(e.category) = LOWER(:category))
                  AND (:status IS NULL OR e.status = :status)
                  AND (:startFrom IS NULL OR e.start_time >= :startFrom)
                  AND (:startTo IS NULL OR e.start_time <= :startTo)
                GROUP BY e.id
            ) x
            """,
            nativeQuery = true)
    Page<EventInfo> searchOrderByPopularityAsc(
            @Param("keyword") String keyword,
            @Param("category") String category,
            @Param("status") Byte status,
            @Param("startFrom") java.time.LocalDateTime startFrom,
            @Param("startTo") java.time.LocalDateTime startTo,
            Pageable pageable
    );

    @Query(value = """
            SELECT e.* FROM event_info e
            LEFT JOIN order_info o ON o.event_id = e.id AND o.status = 2
            WHERE (:keyword IS NULL OR LOWER(e.title) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(CAST(e.description AS CHAR)) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(e.venue) LIKE LOWER(CONCAT('%', :keyword, '%'))
                   OR LOWER(e.address) LIKE LOWER(CONCAT('%', :keyword, '%')))
              AND (:category IS NULL OR LOWER(e.category) = LOWER(:category))
              AND (:status IS NULL OR e.status = :status)
              AND (:startFrom IS NULL OR e.start_time >= :startFrom)
              AND (:startTo IS NULL OR e.start_time <= :startTo)
            GROUP BY e.id
            ORDER BY COALESCE(SUM(o.ticket_count), 0) DESC
            """,
            countQuery = """
            SELECT COUNT(*) FROM (
                SELECT e.id FROM event_info e
                LEFT JOIN order_info o ON o.event_id = e.id AND o.status = 2
                WHERE (:keyword IS NULL OR LOWER(e.title) LIKE LOWER(CONCAT('%', :keyword, '%'))
                       OR LOWER(CAST(e.description AS CHAR)) LIKE LOWER(CONCAT('%', :keyword, '%'))
                       OR LOWER(e.venue) LIKE LOWER(CONCAT('%', :keyword, '%'))
                       OR LOWER(e.address) LIKE LOWER(CONCAT('%', :keyword, '%')))
                  AND (:category IS NULL OR LOWER(e.category) = LOWER(:category))
                  AND (:status IS NULL OR e.status = :status)
                  AND (:startFrom IS NULL OR e.start_time >= :startFrom)
                  AND (:startTo IS NULL OR e.start_time <= :startTo)
                GROUP BY e.id
            ) x
            """,
            nativeQuery = true)
    Page<EventInfo> searchOrderByPopularityDesc(
            @Param("keyword") String keyword,
            @Param("category") String category,
            @Param("status") Byte status,
            @Param("startFrom") java.time.LocalDateTime startFrom,
            @Param("startTo") java.time.LocalDateTime startTo,
            Pageable pageable
    );

    // 首页最近活动：仅启用的活动，开始时间在当前之后，按开始时间升序
    Page<EventInfo> findByStatusAndStartTimeAfterOrderByStartTimeAsc(Byte status, java.time.LocalDateTime startTime, org.springframework.data.domain.Pageable pageable);
}